package zy.dao.sort.settle.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.sort.settle.SortSettleDAO;
import zy.entity.sort.allot.T_Sort_Allot;
import zy.entity.sort.fee.T_Sort_Fee;
import zy.entity.sort.settle.T_Sort_Settle;
import zy.entity.sort.settle.T_Sort_SettleList;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;

@Repository
public class SortSettleDAOImpl extends BaseDaoImpl implements SortSettleDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object st_ar_state = params.get("st_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object st_shop_code = params.get("st_shop_code");
		Object st_manager = params.get("st_manager");
		Object st_number = params.get("st_number");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1)");
		sql.append(" FROM t_sort_settle t");
		sql.append(" JOIN t_base_shop sp ON sp_code = st_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type)){//总公司
			sql.append(" WHERE 1 = 1");
			sql.append(" AND (sp_shop_type = "+CommonUtil.TWO);
			sql.append(" OR (sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+"))");
			sql.append(")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+")");
		}else {
			sql.append(" WHERE 1 = 2");
		}
		if (StringUtil.isNotEmpty(st_ar_state)) {
			sql.append(" AND st_ar_state = :st_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND st_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND st_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(st_shop_code)) {
			sql.append(" AND st_shop_code = :st_shop_code ");
		}
		if (StringUtil.isNotEmpty(st_manager)) {
			sql.append(" AND st_manager = :st_manager ");
		}
		if (StringUtil.isNotEmpty(st_number)) {
			sql.append(" AND INSTR(st_number,:st_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
	}

	@Override
	public List<T_Sort_Settle> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object st_ar_state = params.get("st_ar_state");
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object st_shop_code = params.get("st_shop_code");
		Object st_manager = params.get("st_manager");
		Object st_number = params.get("st_number");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_shop_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_received,st_receivedmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_sort_settle t");
		sql.append(" JOIN t_base_shop sp ON sp_code = st_shop_code AND sp.companyid = t.companyid");
		if(CommonUtil.ONE.equals(shop_type)){//总公司
			sql.append(" WHERE 1 = 1");
			sql.append(" AND (sp_shop_type = "+CommonUtil.TWO);
			sql.append(" OR (sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+"))");
			sql.append(")");
		}else if(CommonUtil.TWO.equals(shop_type)){//分公司
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_upcode = :shop_code AND sp_shop_type IN("+CommonUtil.FOUR+","+CommonUtil.FIVE+")");
		}else {
			sql.append(" WHERE 1 = 2");
		}
		if (StringUtil.isNotEmpty(st_ar_state)) {
			sql.append(" AND st_ar_state = :st_ar_state ");
		}
		if (StringUtil.isNotEmpty(begindate)) {
			sql.append(" AND st_date >= :begindate ");
		}
		if (StringUtil.isNotEmpty(enddate)) {
			sql.append(" AND st_date <= :enddate ");
		}
		if (StringUtil.isNotEmpty(st_shop_code)) {
			sql.append(" AND st_shop_code = :st_shop_code ");
		}
		if (StringUtil.isNotEmpty(st_manager)) {
			sql.append(" AND st_manager = :st_manager ");
		}
		if (StringUtil.isNotEmpty(st_number)) {
			sql.append(" AND INSTR(st_number,:st_number) > 0 ");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY st_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_Settle.class));
	}

	@Override
	public T_Sort_Settle load(Integer st_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_shop_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_received,st_receivedmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_sort_settle t");
		sql.append(" JOIN t_base_shop sp ON sp_code = st_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE st_id = :st_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("st_id", st_id),
					new BeanPropertyRowMapper<>(T_Sort_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public T_Sort_Settle load(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_shop_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_received,st_receivedmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid,sp_name AS shop_name,");
		sql.append(" (SELECT ba_name FROM t_money_bank ba WHERE ba_code = st_ba_code AND ba.companyid = t.companyid LIMIT 1) AS ba_name");
		sql.append(" FROM t_sort_settle t");
		sql.append(" JOIN t_base_shop sp ON sp_code = st_shop_code AND sp.companyid = t.companyid");
		sql.append(" WHERE st_number = :st_number");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("st_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Sort_Settle check(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id,st_number,st_date,st_shop_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_received,st_receivedmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,t.companyid");
		sql.append(" FROM t_sort_settle t");
		sql.append(" WHERE st_number = :st_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(),
					new MapSqlParameterSource().addValue("st_number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public T_Sort_Settle check_settle(String sp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT st_id");
		sql.append(" FROM t_sort_settle");
		sql.append(" WHERE st_ar_state != 1");
		sql.append(" AND st_shop_code = :sp_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_Settle.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public List<T_Sort_SettleList> load_sort_forsavetemp(String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT * FROM");
		sql.append(" (SELECT at_sendmoney AS stl_receivable,");
		sql.append(" at_received AS stl_received,");
		sql.append(" at_discount_money AS stl_discount_money_yet,");
		sql.append(" at_prepay AS stl_prepay_yet,");
		sql.append(" at_receivable AS stl_unreceivable,");
		sql.append(" 0 AS stl_discount_money,");
		sql.append(" 0 AS stl_prepay,");
		sql.append(" 0 AS stl_real_received,");
		sql.append(" at_number AS stl_bill_number,");
		sql.append(" at_type AS stl_type,");
		sql.append(" at_ar_state AS stl_ar_state,");
		sql.append(" (CASE WHEN at_type = 0 THEN 3");
		sql.append(" WHEN at_type = 1 THEN 1");
		sql.append(" WHEN at_type = 2 THEN IF(at_receivable<0,1,2)");
		sql.append(" ELSE 4 END) AS new_order,");
		sql.append(" IF(at_ar_state IN (3,5),2,1) AS ar_order");
		sql.append(" FROM t_sort_allot");
		sql.append(" WHERE 1=1");
		sql.append(" AND at_shop_code = :sp_code");
		sql.append(" AND ((at_type = 0 AND at_ar_state IN(3,4,5))");
		sql.append(" OR(at_type IN(1,2) AND at_ar_state = 4))");
		sql.append(" AND at_pay_state != 2");
		sql.append(" AND companyid = :companyid");
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_money AS stl_receivable,");
		sql.append(" fe_received AS stl_received,");
		sql.append(" fe_discount_money AS stl_discount_money_yet,");
		sql.append(" fe_prepay AS stl_prepay_yet,");
		sql.append(" fe_receivable AS stl_unreceivable,");
		sql.append(" 0 AS stl_discount_money,");
		sql.append(" 0 AS stl_prepay,");
		sql.append(" 0 AS stl_real_received,");
		sql.append(" fe_number AS stl_bill_number,");
		sql.append(" 3 AS stl_type,");
		sql.append(" fe_ar_state AS stl_ar_state,");
		sql.append(" IF(fe_receivable<0,1,3) AS new_order,1 AS ar_order");
		sql.append(" FROM t_sort_fee");
		sql.append(" WHERE 1=1");
		sql.append(" AND fe_shop_code = :sp_code");
		sql.append(" AND fe_ar_state = 1");
		sql.append(" AND fe_pay_state != 2");
		sql.append(" AND companyid = :companyid");
		sql.append(" )t");
		sql.append(" ORDER BY ar_order ASC,new_order ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
	}

	@Override
	public List<T_Sort_SettleList> temp_list(Map<String, Object> params) {
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_join,stl_ar_state,stl_us_id,companyid ");
		sql.append(" FROM t_sort_settlelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY stl_id ASC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
	}
	
	@Override
	public List<T_Sort_SettleList> temp_list_forentire(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_join,stl_ar_state,stl_us_id,companyid ");
		sql.append(" FROM t_sort_settlelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_ar_state NOT IN(3,5)");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY stl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
	}
	
	@Override
	public List<T_Sort_SettleList> temp_list_forsave(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_join,stl_ar_state,stl_us_id,companyid ");
		sql.append(" FROM t_sort_settlelist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_discount_money+stl_prepay+stl_real_received != 0");
		sql.append(" AND stl_join = 1");
		sql.append(" AND stl_ar_state NOT IN(3,5)");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY stl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_us_id", us_id).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
	}
	
	@Override
	public void temp_save(List<T_Sort_SettleList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_sort_settlelist_temp");
		sql.append(" (stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_join,stl_ar_state,stl_us_id,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:stl_receivable,:stl_received,:stl_discount_money_yet,:stl_prepay_yet,:stl_unreceivable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_received,:stl_bill_number,:stl_remark,:stl_type,:stl_join,:stl_ar_state,:stl_us_id,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}
	
	@Override
	public void temp_update(List<T_Sort_SettleList> temps) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_discount_money = :stl_discount_money");
		sql.append(" ,stl_prepay = :stl_prepay");
		sql.append(" ,stl_real_received = :stl_real_received");
		sql.append(" ,stl_join = :stl_join");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
	}

	@Override
	public void temp_clear(Integer us_id,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_sort_settlelist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_us_id = :stl_us_id");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),
				new MapSqlParameterSource().addValue("stl_us_id", us_id).addValue("companyid", companyid));
	}
	
	@Override
	public void temp_updateDiscountMoney(T_Sort_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_discount_money = :stl_discount_money");
		if(temp.getStl_real_received() != null){
			sql.append(" ,stl_real_received = :stl_real_received");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updatePrepay(T_Sort_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_prepay = :stl_prepay");
		if(temp.getStl_real_received() != null){
			sql.append(" ,stl_real_received = :stl_real_received");
		}
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRealMoney(T_Sort_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_real_received = :stl_real_received");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateRemark(T_Sort_SettleList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_remark = :stl_remark");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_id = :stl_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateJoin(String ids,String stl_join) {
		String[] idArr = ids.split(",");
		MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
		sqlParameterSource.addValue("stl_join", stl_join);
		if (idArr.length > 1) {
			sqlParameterSource.addValue("stl_id", Arrays.asList(idArr));
		} else {
			sqlParameterSource.addValue("stl_id", ids);
		}
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_settlelist_temp");
		sql.append(" SET stl_join = :stl_join");
		sql.append(" WHERE 1=1");
		if (idArr.length > 1) {
			sql.append(" AND stl_id IN (:stl_id)");
		}else {
			sql.append(" AND stl_id = :stl_id");
		}
		namedParameterJdbcTemplate.update(sql.toString(), sqlParameterSource);
	}

	@Override
	public List<T_Sort_SettleList> detail_list(String number,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_number,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_ar_state,companyid");
		sql.append(" FROM t_sort_settlelist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND companyid = :companyid");
		sql.append(" ORDER BY stl_id ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid), 
				new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
	}
	
	@Override
	public void save(T_Sort_Settle settle, List<T_Sort_SettleList> details) {
		String prefix = CommonUtil.NUMBER_PREFIX_SORT_SETTLE + DateUtil.getYearMonthDateYYYYMMDD();
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT CONCAT(:prefix,f_addnumber(MAX(st_number))) AS new_number");
		sql.append(" FROM t_sort_settle");
		sql.append(" WHERE 1=1");
		sql.append(" AND INSTR(st_number,:prefix) > 0");
		sql.append(" AND companyid = :companyid");
		String new_number = namedParameterJdbcTemplate.queryForObject(sql.toString(), 
				new MapSqlParameterSource().addValue("prefix", prefix).addValue("companyid", settle.getCompanyid()), String.class);
		settle.setSt_number(new_number);
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_settle");
		sql.append(" (st_number,st_date,st_shop_code,st_maker,st_manager,st_ba_code,st_discount_money,st_prepay,st_received,st_receivedmore,");
		sql.append(" st_remark,st_ar_state,st_ar_date,st_entire,st_pp_number,st_leftdebt,st_sysdate,st_us_id,companyid)");
		sql.append(" VALUES");
		sql.append(" (:st_number,:st_date,:st_shop_code,:st_maker,:st_manager,:st_ba_code,:st_discount_money,:st_prepay,:st_received,:st_receivedmore,");
		sql.append(" :st_remark,:st_ar_state,:st_ar_date,:st_entire,:st_pp_number,:st_leftdebt,:st_sysdate,:st_us_id,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle),holder);
		settle.setSt_id(holder.getKey().intValue());
		for(T_Sort_SettleList item:details){
			item.setStl_number(settle.getSt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_settlelist");
		sql.append(" (stl_number,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_ar_state,companyid)");
		sql.append(" VALUES");
		sql.append(" (:stl_number,:stl_receivable,:stl_received,:stl_discount_money_yet,:stl_prepay_yet,:stl_unreceivable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_received,:stl_bill_number,:stl_remark,:stl_type,:stl_ar_state,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void update(T_Sort_Settle settle, List<T_Sort_SettleList> details) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_settle");
		sql.append(" SET st_date=:st_date");
		sql.append(" ,st_maker=:st_maker");
		sql.append(" ,st_manager=:st_manager");
		sql.append(" ,st_ba_code=:st_ba_code");
		sql.append(" ,st_discount_money=:st_discount_money");
		sql.append(" ,st_prepay=:st_prepay");
		sql.append(" ,st_received=:st_received");
		sql.append(" ,st_receivedmore=:st_receivedmore");
		sql.append(" ,st_remark=:st_remark");
		sql.append(" ,st_ar_state=:st_ar_state");
		sql.append(" ,st_ar_date=:st_ar_date");
		sql.append(" ,st_entire=:st_entire");
		sql.append(" ,st_pp_number=:st_pp_number");
		sql.append(" ,st_leftdebt=:st_leftdebt");
		sql.append(" ,st_us_id=:st_us_id");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
		for(T_Sort_SettleList item:details){
			item.setStl_number(settle.getSt_number());
		}
		sql.setLength(0);
		sql.append("INSERT INTO t_sort_settlelist");
		sql.append(" (stl_number,stl_receivable,stl_received,stl_discount_money_yet,stl_prepay_yet,stl_unreceivable,");
		sql.append(" stl_discount_money,stl_prepay,stl_real_received,stl_bill_number,stl_remark,stl_type,stl_ar_state,companyid)");
		sql.append(" VALUES");
		sql.append(" (:stl_number,:stl_receivable,:stl_received,:stl_discount_money_yet,:stl_prepay_yet,:stl_unreceivable,");
		sql.append(" :stl_discount_money,:stl_prepay,:stl_real_received,:stl_bill_number,:stl_remark,:stl_type,:stl_ar_state,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(details.toArray()));
	}
	
	@Override
	public void updateApprove(T_Sort_Settle settle) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_settle");
		sql.append(" SET st_ar_state=:st_ar_state");
		sql.append(" ,st_ar_date = :st_ar_date");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
	}
	
	@Override
	public void updatePpNumber(T_Sort_Settle settle) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_sort_settle");
		sql.append(" SET st_pp_number=:st_pp_number");
		sql.append(" WHERE st_id=:st_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(settle));
	}
	
	@Override
	public List<T_Sort_Allot> listAllotBySettle(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT at_id,at_sendmoney,");
		sql.append(" at_discount_money + stl_discount_money AS at_discount_money,");
		sql.append(" at_prepay + stl_prepay AS at_prepay,");
		sql.append(" at_received + stl_real_received AS at_received,");
		sql.append(" at_receivable - (stl_discount_money+stl_prepay+stl_real_received) AS at_receivable");
		sql.append(" FROM t_sort_settlelist t");
		sql.append(" JOIN t_sort_allot at ON at_number = stl_bill_number AND at.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type != 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_Allot.class));
	}
	
	@Override
	public List<T_Sort_Allot> listAllotBySettle_Reverse(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT at_id,at_sendmoney,");
		sql.append(" at_discount_money - stl_discount_money AS at_discount_money,");
		sql.append(" at_prepay - stl_prepay AS at_prepay,");
		sql.append(" at_received - stl_real_received AS at_received,");
		sql.append(" at_receivable + (stl_discount_money+stl_prepay+stl_real_received) AS at_receivable");
		sql.append(" FROM t_sort_settlelist t");
		sql.append(" JOIN t_sort_allot at ON at_number = stl_bill_number AND at.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type != 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_Allot.class));
	}
	
	@Override
	public List<T_Sort_Fee> listFeeBySettle(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT fe_id,fe_money,");
		sql.append(" fe_discount_money + stl_discount_money AS fe_discount_money,");
		sql.append(" fe_prepay + stl_prepay AS fe_prepay,");
		sql.append(" fe_received + stl_real_received AS fe_received,");
		sql.append(" fe_receivable-(stl_discount_money+stl_prepay+stl_real_received) AS fe_receivable");
		sql.append(" FROM t_sort_settlelist t");
		sql.append(" JOIN t_sort_fee fe ON fe_number = stl_bill_number AND fe.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type = 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_Fee.class));
	}
	
	@Override
	public List<T_Sort_Fee> listFeeBySettle_Reverse(String number,Integer companyid){
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT fe_id,fe_money,");
		sql.append(" fe_discount_money - stl_discount_money AS fe_discount_money,");
		sql.append(" fe_prepay - stl_prepay AS fe_prepay,");
		sql.append(" fe_received - stl_real_received AS fe_received,");
		sql.append(" fe_receivable+(stl_discount_money+stl_prepay+stl_real_received) AS fe_receivable");
		sql.append(" FROM t_sort_settlelist t");
		sql.append(" JOIN t_sort_fee fe ON fe_number = stl_bill_number AND fe.companyid = t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND stl_type = 3");
		sql.append(" AND stl_number = :stl_number");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), 
				new MapSqlParameterSource().addValue("stl_number", number).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Sort_Fee.class));
	}
	
	@Override
	public void updateAllotBySettle(List<T_Sort_Allot> allots) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_allot");
		sql.append(" SET at_discount_money = :at_discount_money");
		sql.append(" ,at_prepay = :at_prepay");
		sql.append(" ,at_received = :at_received");
		sql.append(" ,at_receivable = :at_receivable");
		sql.append(" ,at_pay_state = :at_pay_state");
		sql.append(" WHERE 1=1");
		sql.append(" AND at_id = :at_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(allots.toArray()));
	}
	
	@Override
	public void updateFeeBySettle(List<T_Sort_Fee> fees) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_sort_fee");
		sql.append(" SET fe_discount_money = :fe_discount_money");
		sql.append(" ,fe_prepay = :fe_prepay");
		sql.append(" ,fe_received = :fe_received");
		sql.append(" ,fe_receivable = :fe_receivable");
		sql.append(" ,fe_pay_state = :fe_pay_state");
		sql.append(" WHERE 1=1");
		sql.append(" AND fe_id = :fe_id");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(fees.toArray()));
	}
	
	@Override
	public void del(String st_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_settle");
		sql.append(" WHERE st_number=:st_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("st_number", st_number).addValue("companyid", companyid));
		sql.setLength(0);
		sql.append(" DELETE FROM t_sort_settlelist");
		sql.append(" WHERE stl_number=:stl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("stl_number", st_number).addValue("companyid", companyid));
	}
	
	@Override
	public void deleteList(String st_number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_sort_settlelist");
		sql.append(" WHERE stl_number=:stl_number");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("stl_number", st_number).addValue("companyid", companyid));
	}
	
	@Override
	public T_Sort_SettleList check_settle_bill(String number, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT stl_id,stl_number");
		sql.append(" FROM t_sort_settlelist");
		sql.append(" WHERE stl_bill_number = :number");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("number", number).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Sort_SettleList.class));
		} catch (Exception e) {
			return null;
		}
	}
	
}
